﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NTQOnlineExam.Services.Infrastructure;
using NTQOnlineExam.Services.Models;
using NTQOnlineExam.Services.SearchCriteria;

namespace NTQOnlineExam.Services.ReportService.Order
{
    public class SearchOrderByCriteriaDbCommand : ICommand<OrderSearchResult>
    {
        private readonly OrderSearchCriteria _criteria;
        private readonly int _pageSize;
        private readonly int _pageIndex;
        private const string _sql = @"
                 WITH PAGING AS (
                    SELECT o.OrderId {2}, row_number() over (order by {0}) as rownumber
                    FROM dbo.[Order] o LEFT JOIN dbo.[UserProfile] u ON o.UserId = u.UserId {1}
                )
        ";

        public SearchOrderByCriteriaDbCommand(OrderSearchCriteria criteria, int pageSize, int pageIndex)
        {
            _criteria = criteria;
            _pageSize = pageSize;
            _pageIndex = pageIndex;
        }

        public OrderSearchResult Execute()
        {
            var connectionString = ConfigurationManager.AppSettings["ConnectionString"];
            using (var connection = new SqlConnection(connectionString))
            {
                IList<OrderDTO> items = new List<OrderDTO>();
                int totalCount = 0;
                decimal totalOrderAmount = 0;
                var cmd = new SqlCommand(BuildQuery(), connection);
                cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = _pageIndex;
                cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = _pageSize;
                if (!string.IsNullOrWhiteSpace(_criteria.Keyword)) cmd.Parameters.Add("@Keyword", SqlDbType.NVarChar).Value = _criteria.Keyword;
                if (_criteria.Status.HasValue) cmd.Parameters.Add("@OrderStatus", SqlDbType.Int).Value = _criteria.Status.Value;
                if (_criteria.OrderType.HasValue) cmd.Parameters.Add("@OrderType", SqlDbType.Int).Value = _criteria.OrderType.Value;
                if (_criteria.FromDate.HasValue) cmd.Parameters.Add("@FromDate", SqlDbType.DateTime).Value = _criteria.FromDate.Value;
                if (_criteria.ToDate.HasValue) cmd.Parameters.Add("@ToDate", SqlDbType.DateTime).Value = _criteria.ToDate.Value.AddHours(23).AddMinutes(59);
                connection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                try
                {
                    if (!reader.Read()) return null;
                    totalOrderAmount = GetTotalOrderAmount(reader, out totalCount);
                    reader.NextResult();

                    while (reader.Read())
                    {
                        items.Add(GetDto(reader));
                    }
                }
                finally
                {
                    reader.Close();
                }
                return new OrderSearchResult() { Result = new SearchResult<OrderDTO>(items, totalCount), TotalOrderAmount = totalOrderAmount };
            }
        }

        private decimal GetTotalOrderAmount(SqlDataReader reader,out int rowCount)
        {
            rowCount = (int) reader["TotalRowItems"];
            return (decimal) reader["TotalOrderAmount"];
        }

        private OrderDTO GetDto(SqlDataReader reader)
        {
            var order = new OrderDTO()
            {
                OrderUniqueId = (int)reader["OrderUniqueId"],
                OrderId = (Guid)reader["OrderId"],
                Email = reader["Email"].ToString(),
                FirstName = reader["FirstName"].ToString(),
                LastName = reader["LastName"].ToString(),
            };
            order.SubTotal = reader.GetDecimal(reader.GetOrdinal("SubTotal"));
            order.CreationDate = reader.GetDateTime(reader.GetOrdinal("CreationDate"));
            order.Status = reader.GetInt32(reader.GetOrdinal("Status"));
            order.OrderType = reader.GetInt32(reader.GetOrdinal("OrderType"));
            return order;
        }


        private string BuildQuery()
        {
            var query = new StringBuilder();
            var sqlCount = string.Format(_sql, BuildOrderClause(), BuildWhereClause(), ", o.SubTotal");
            var sqlQuery = string.Format(_sql, BuildOrderClause(), BuildWhereClause(), ", o.Email, u.FirstName, u.LastName, o.SubTotal, o.CreationDate, o.Status, o.OrderUniqueId, o.OrderType");
            query.AppendFormat("{0} {1};{2} {3}", sqlCount, @"SELECT COUNT(1) as TotalRowItems,ISNULL(SUM(SubTotal),0) as TotalOrderAmount  FROM paging", sqlQuery, @" SELECT * FROM paging WHERE (@PageSize=0 or rownumber between @PageSize*@PageIndex+1 and @PageSize*(@PageIndex+1)) ORDER BY rownumber");
            return query.ToString();
        }

        private string BuildWhereClause()
        {
            string whereClause = string.Empty;
            if (!string.IsNullOrEmpty(_criteria.Keyword))
            {
                int temp = 0;
                if (int.TryParse(_criteria.Keyword, out temp))
                {
                    whereClause = string.Format(@" AND o.OrderUniqueId = {0}", temp);
                }
                else
                {
                    whereClause = @" AND (o.Email LIKE '%' + @Keyword +'%' 
                                    OR u.FirstName LIKE '%'+ @Keyword +'%' 
                                    OR u.LastName LIKE '%'+ @Keyword +'%' 
                                    OR o.Email LIKE '%'+ @Keyword +'%' 
                                    OR (u.FirstName + ' ' + u.LastName) LIKE '%'+ @Keyword +'%' 
                                  )
                                ";
                }
            }
            if (_criteria.Status.HasValue)
            {
                whereClause += @" AND o.Status = @OrderStatus";
            }
            if (_criteria.OrderType.HasValue)
            {
                whereClause += @" AND o.OrderType = @OrderType";
            }
            if (_criteria.FromDate.HasValue)
            {
                whereClause += @" AND o.CreationDate >= @FromDate";
            }

            if (_criteria.ToDate.HasValue)
            {
                whereClause += @" AND o.CreationDate <= @ToDate";
            }

            if (whereClause.Trim().StartsWith("AND", StringComparison.InvariantCultureIgnoreCase))
                whereClause = whereClause.Trim().Remove(0, 3);

            if (!String.IsNullOrEmpty(whereClause))
                return " WHERE " + whereClause;
            return string.Empty;
        }

        private string BuildOrderClause()
        {
            var orderClause = new StringBuilder();
            foreach (SearchSortItem sortItem in _criteria.SortItems)
            {
                if (sortItem.SortExpression == "FullName")
                {
                    orderClause.AppendFormat("u.FirstName {0}, u.LastName {0}", sortItem.SortOrder == SortOrder.Ascending ? "ASC" : "DESC");
                    orderClause.Append(',');
                }
                else if (sortItem.SortExpression == "SubTotalDisplay")
                {
                    orderClause.AppendFormat("o.SubTotal {0}", sortItem.SortOrder == SortOrder.Ascending ? "ASC" : "DESC");
                    orderClause.Append(',');
                }
                else if (sortItem.SortExpression == "CreationDateDisplay")
                {
                    orderClause.AppendFormat("o.CreationDate {0}", sortItem.SortOrder == SortOrder.Ascending ? "ASC" : "DESC");
                    orderClause.Append(',');
                }
                else
                {
                    orderClause.AppendFormat("o.{0} {1}", sortItem.SortExpression, sortItem.SortOrder == SortOrder.Ascending ? "ASC" : "DESC");
                    orderClause.Append(',');
                }

            }

            if (orderClause.Length <= 0)
            {
                orderClause.Append("OrderUniqueId DESC");
            }
            else
            {
                orderClause.Remove(orderClause.Length - 1, 1);
            }
            return orderClause.ToString();
        }
    }
}
